﻿Public Class RIGHTPRDPRICE
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection

    Private PRCID = Nothing
    Private PRDCODE = Nothing
    Private MTRGTID = Nothing
    Private STRGTID = Nothing
    Private SBRGTID = Nothing
    Private OPDPRC = Nothing
    Private IPDPRC = Nothing
    Private STDATE As Date = Nothing
    Private ENDATE As Date = Nothing
    Private STATUS = Nothing
    Private selectPrdprice = "SELECT `prcid` AS 'รหัสรายการราคา', `prdcode` AS 'รหัสสินค้า', `mtrgtid` AS 'หมวดสิทธิ์', `strgtid` AS 'กลุ่มสิทธิ์', `sbrgtid` AS 'ชื่อสิทธิ์', `opdprc` AS 'ราคาผู้ป่วยนอก', `ipdprc` AS 'ราคาผู้ป่วยใน', `stdate` AS 'วันที่เริ่มต้นโปรโมชั่น', `endate` AS 'วันที่สิ้นสุดโปรโมชั่น', `status` AS 'สถานะการเปิดใช้งาน' FROM prdprice"
    Private selectMasproductWithDateAndRight = "SELECT a.`prdcode` AS 'รหัสผลิตภัณฑ์', a.`prdname` AS 'ชื่อผลิตภัณฑ์', a.`prdcat` AS 'รหัสกลุ่มผลิตภัณฑ์', b.`catname` AS 'กลุ่มผลิตภัณฑ์', c.`st` AS 'วันเริ่มต้น', c.`en` AS 'วันสิ้นสุด', a.`last_cost` AS 'ต้นทุนสุดท้าย', a.`avg_cost` AS 'ต้นทุนเฉลี่ย', c.`opd_price` AS 'ตั้งต้น OPD', c.`ipd_price` AS 'ตั้งต้น IPD', c.`prcid` AS 'รหัสราคา' FROM masproduct a LEFT JOIN masprdcats b ON a.`prdcat` = b.`prdcat` LEFT JOIN (SELECT * FROM masprice WHERE NOW() BETWEEN ST AND EN) c ON a.`prdcode` = c.`prdcode`"

    Private PCKPID = Nothing

    Property PRCID_ As Integer
        Get
            Return PRCID
        End Get
        Set(value As Integer)
            PRCID = value
        End Set
    End Property

    Property PRDCODE_ As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property MTRGTID_ As Integer
        Get
            Return MTRGTID
        End Get
        Set(value As Integer)
            MTRGTID = value
        End Set
    End Property

    Property STRGTID_ As Integer
        Get
            Return STRGTID
        End Get
        Set(value As Integer)
            STRGTID = value
        End Set
    End Property

    Property SBRGTID_ As Integer
        Get
            Return SBRGTID
        End Get
        Set(value As Integer)
            SBRGTID = value
        End Set
    End Property

    Property OPDPRC_ As Double
        Get
            Return OPDPRC
        End Get
        Set(value As Double)
            OPDPRC = value
        End Set
    End Property

    Property IPDPRC_ As Double
        Get
            Return IPDPRC
        End Get
        Set(value As Double)
            IPDPRC = value
        End Set
    End Property

    Property STDATE_ As Date
        Get
            Return STDATE
        End Get
        Set(value As Date)
            STDATE = value
        End Set
    End Property

    Property ENDATE_ As Date
        Get
            Return ENDATE
        End Get
        Set(value As Date)
            ENDATE = value
        End Set
    End Property

    Property STATUS_ As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property selectPrdprice_ As String
        Get
            Return selectPrdprice
        End Get
        Set(value As String)

        End Set
    End Property

    Property selectMasproductWithDateAndRight_ As String
        Get
            Return selectMasproductWithDateAndRight
        End Get
        Set(value As String)

        End Set
    End Property

    Property PCKPID_ As Integer
        Get
            Return PCKPID
        End Get
        Set(value As Integer)
            PCKPID = value
        End Set
    End Property

    Public Sub addPrdpricePackage()
        Dim productData As DataTable
        productData = connect.GetTable("SELECT `pcklstid` AS 'queryPCKLSTID', `prdcode` AS 'queryPRDCODE' FROM pcklistproduct WHERE `pckpid` = '" & PCKPID & "' AND `status` = '1'")

        connect.BeginTrans()

        For i As Integer = 0 To productData.Rows.Count - 1
            PRDCODE = productData.Rows(i)("queryPRDCODE")
            Dim checkCase As String
            checkCase = "SELECT `prcid` As 'queryPRCID' FROM prdprice WHERE `prdcode` = '" & PRDCODE & "' AND `mtrgtid` = '" & MTRGTID & "' "
            If MTRGTID <> 0 Then
                checkCase += "AND `strgtid` = '" & STRGTID & "' "
            ElseIf MTRGTID = 0 Then
                checkCase += "AND `strgtid` IS NULL "
            End If
            If connect.GetTable(checkCase).Rows.Count > 0 Then
                'If connect.GetTable("SELECT `PRCID` As 'queryPRCID' FROM prdprice WHERE `PRDCODE` = '" & productData(i)("queryPRDCODE") & "' AND `MTRGTID` = '" & MTRGTID & "' AND `STRGTID` = '" & STRGTID & "' AND `SBRGTID` = '" & SBRGTID & "'").Rows.Count > 0 Then
                'MsgBox("ซ้ำ, อัพเดต" & productData(i)("queryPRDCODE"))

                Dim sqlDeleteOldPrice As String
                sqlDeleteOldPrice = "DELETE FROM prdprice WHERE "

                If MTRGTID <> 0 Then
                    sqlDeleteOldPrice += "mtrgtid = '" & MTRGTID & "' "
                Else
                    sqlDeleteOldPrice += "mtrgtid is null "
                End If

                If STRGTID <> 0 Then
                    sqlDeleteOldPrice += "AND strgtid = '" & STRGTID & "' "
                Else
                    sqlDeleteOldPrice += "AND strgtid is null "
                End If

                If SBRGTID <> 0 Then
                    sqlDeleteOldPrice += "AND sbrgtid = '" & SBRGTID & "' "
                Else
                    sqlDeleteOldPrice += "AND sbrgtid is null "
                End If

                sqlDeleteOldPrice += "AND prdcode = '" & productData(i)("queryPRDCODE") & "' "
                sqlDeleteOldPrice += "AND stdate > NOW()"

                'connect.ExecuteNonQuery("DELETE FROM prdprice WHERE mtrgtid = '" & MTRGTID & "' AND strgtid = '" & STRGTID & "' AND sbrgtid = '" & SBRGTID & "' AND prdcode = '" & productData(i)("queryPRDCODE") & "' AND stdate > NOW()")

                connect.ExecuteNonQuery(sqlDeleteOldPrice)

                Dim STDATEMinusYear As Date = STDATE.AddYears(-543)
                Dim ENDATEMinusYear As Date = STDATEMinusYear.AddDays(-1)

                connect.ExecuteNonQuery("UPDATE prdprice SET endate = '" & ENDATEMinusYear.ToString("yyyy-MM-dd") & "' WHERE mtrgtid = '" & MTRGTID & "' AND strgtid = '" & STRGTID & "' AND sbrgtid = '" & SBRGTID & "' AND stdate <= NOW() AND endate >= NOW()")

                PRDCODE = productData.Rows(i)("queryPRDCODE")

                Dim queryString As String
                queryString = "INSERT INTO prdprice "
                queryString += "(prdcode, mtrgtid, strgtid, sbrgtid, proid, f_pro, opdprc, ipdprc, stdate, endate, status) "
                queryString += "VALUES ("
                queryString += "'" & PRDCODE & "', "
                queryString += "'" & MTRGTID & "', "

                If STRGTID <> 0 Then
                    queryString += "'" & STRGTID & "', "
                Else
                    queryString += "null, "
                End If

                If SBRGTID <> 0 Then
                    queryString += "'" & SBRGTID & "', "
                Else
                    queryString += "null, "
                End If

                queryString += "null, "
                queryString += "'0', "
                queryString += "'" & OPDPRC & "', "
                queryString += "'" & IPDPRC & "', "
                queryString += "'" & STDATEMinusYear.ToString("yyyy-MM-dd") & "', "
                queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "
                queryString += "'" & STATUS & "')"

                connect.ExecuteNonQuery(queryString)
            ElseIf connect.GetTable(checkCase).Rows.Count = 0 Then
                'ElseIf connect.GetTable("SELECT `PRCID` As 'queryPRCID' FROM prdprice WHERE `PRDCODE` = '" & productData(i)("queryPRDCODE") & "' AND `MTRGTID` = '" & MTRGTID & "' AND `STRGTID` = '" & STRGTID & "' AND `SBRGTID` = '" & SBRGTID & "'").Rows.Count = 0 Then
                'MsgBox("ไม่ซ้ำ, อินเสิท" & productData(i)("queryPRDCODE"))

                PRDCODE = productData.Rows(i)("queryPRDCODE")

                Dim queryString As String
                queryString = "INSERT INTO prdprice "
                queryString += "(prdcode, mtrgtid, strgtid, sbrgtid, proid, f_pro, opdprc, ipdprc, stdate, endate, status) "
                queryString += "VALUES ("
                queryString += "'" & PRDCODE & "', "
                queryString += "'" & MTRGTID & "', "

                If STRGTID <> 0 Then
                    queryString += "'" & STRGTID & "', "
                Else
                    queryString += "null, "
                End If

                If SBRGTID <> 0 Then
                    queryString += "'" & SBRGTID & "', "
                Else
                    queryString += "null, "
                End If

                queryString += "null, "
                queryString += "'0', "
                queryString += "'" & OPDPRC & "', "
                queryString += "'" & IPDPRC & "', "


                Dim STDATEMinusYear As Date = STDATE.AddYears(-543)

                queryString += "'" & STDATEMinusYear.ToString("yyyy-MM-dd") & "', "
                queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "
                queryString += "'" & STATUS & "')"

                connect.ExecuteNonQuery(queryString)
            End If
        Next

        Try
            connect.CommitTrans()
            MsgBox("ท่านได้ทำการจับคู่หมวดสินค้ากับคู่สัญญาเรียบร้อยแล้ว")
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(ex.ToString)
        End Try

        connect.Dispose()
    End Sub

    Public Function addPrdprice() As Boolean
        Dim checkCase As String
        checkCase = "SELECT `prcid` As 'queryPRCID' FROM prdprice WHERE `prdcode` = '" & PRDCODE & "' AND `mtrgtid` = '" & MTRGTID & "' "
        If STRGTID <> 0 Then
            checkCase += "AND `strgtid` = '" & STRGTID & "' "
        ElseIf STRGTID = 0 Then
            checkCase += "AND `strgtid` IS NULL "
        End If

        If SBRGTID <> 0 Then
            checkCase += "AND `sbrgtid` = '" & SBRGTID & "' "
        ElseIf SBRGTID = 0 Then
            checkCase += "AND `sbrgtid` IS NULL "
        End If

        Dim datejaja As New FriendsDate()
        datejaja.beforSave()

        If connect.GetTable(checkCase).Rows.Count > 0 Then
            'If connect.GetTable("SELECT `PRCID` As 'queryPRCID' FROM prdprice WHERE `PRDCODE` = '" & PRDCODE & "' AND `MTRGTID` = '" & MTRGTID & "' AND `STRGTID` = '" & STRGTID & "' AND `SBRGTID` = '" & SBRGTID & "'").Rows.Count > 0 Then
            'MsgBox("ซ้ำ")

            connect.BeginTrans()

            Dim deleteFuture As String
            deleteFuture = "DELETE FROM prdprice WHERE "

            If MTRGTID <> 0 Then
                deleteFuture += "mtrgtid = '" & MTRGTID & "' AND "
            Else
                deleteFuture += "mtrgtid IS NULL AND "
            End If

            If STRGTID <> 0 Then
                deleteFuture += "strgtid = '" & STRGTID & "' AND "
            Else
                deleteFuture += "strgtid IS NULL AND "
            End If

            If SBRGTID <> 0 Then
                deleteFuture += "sbrgtid = '" & SBRGTID & "' AND "
            Else
                deleteFuture += "sbrgtid IS NULL AND "
            End If

            deleteFuture += "prdcode = '" & PRDCODE & "' AND stdate > NOW()"


            connect.ExecuteNonQuery(deleteFuture)
            'connect.ExecuteNonQuery("DELETE FROM prdprice WHERE MTRGTID = '" & MTRGTID & "' AND STRGTID = '" & STRGTID & "' AND SBRGTID = '" & SBRGTID & "' AND PRDCODE = '" & PRDCODE & "' AND STDATE > NOW()")

            'Dim STDATEMinusYear As Date = STDATE.AddYears(-543)
            'Dim ENDATEMinusYear As Date = STDATEMinusYear.AddDays(-1)
            Dim realENDATE As Date = STDATE.AddDays(-1)

            Dim updateENDATEofPresent As String
            updateENDATEofPresent = "UPDATE prdprice SET endate = '" & realENDATE.ToString("yyyy-MM-dd") & "' WHERE "

            If MTRGTID <> 0 Then
                updateENDATEofPresent += "mtrgtid = '" & MTRGTID & "' AND "
            Else
                updateENDATEofPresent += "mtrgtid IS NULL AND "
            End If

            If STRGTID <> 0 Then
                updateENDATEofPresent += "strgtid = '" & STRGTID & "' AND "
            Else
                updateENDATEofPresent += "strgtid IS NULL AND "
            End If

            If STRGTID <> 0 Then
                updateENDATEofPresent += "strgtid = '" & STRGTID & "' AND "
            Else
                updateENDATEofPresent += "strgtid IS NULL AND "
            End If

            updateENDATEofPresent += "stdate <= NOW() AND endate >= NOW() AND prdcode = '" & PRDCODE & "'"

            'connect.ExecuteNonQuery("UPDATE prdprice SET ENDATE = '" & ENDATEMinusYear.ToString("yyyy-MM-dd") & "' WHERE MTRGTID = '" & MTRGTID & "' AND STRGTID = '" & STRGTID & "' AND SBRGTID = '" & SBRGTID & "' AND STDATE <= NOW() AND ENDATE >= NOW() AND PRDCODE = '" & PRDCODE & "'")
            connect.ExecuteNonQuery(updateENDATEofPresent)

            Dim queryString As String
            queryString = "INSERT INTO prdprice "
            queryString += "(prdcode, mtrgtid, strgtid, sbrgtid, proid, f_pro, opdprc, ipdprc, stdate, endate, status) "
            queryString += "VALUES ("
            queryString += "'" & PRDCODE & "', "
            queryString += "'" & MTRGTID & "', "

            If STRGTID <> 0 Then
                queryString += "'" & STRGTID & "', "
            Else
                queryString += "null, "
            End If

            If SBRGTID <> 0 Then
                queryString += "'" & SBRGTID & "', "
            Else
                queryString += "null, "
            End If

            queryString += "null, "
            queryString += "'0', "
            queryString += "'" & OPDPRC & "', "
            queryString += "'" & IPDPRC & "', "
            'queryString += "'" & STDATEMinusYear.ToString("yyyy-MM-dd") & "', "
            'queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "
            queryString += "'" & STDATE.ToString("yyyy-MM-dd") & "', "
            queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "
            queryString += "'" & STATUS & "')"

            connect.ExecuteNonQuery(queryString)

            Try
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการกำหนดราคาคู่สัญญาเรียบร้อยแล้ว")
                datejaja.afterSave()
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            End Try

            connect.Dispose()
        ElseIf connect.GetTable(checkCase).Rows.Count = 0 Then
            'ElseIf connect.GetTable("SELECT `PRCID` As 'queryPRCID' FROM prdprice WHERE `PRDCODE` = '" & PRDCODE & "' AND `MTRGTID` = '" & MTRGTID & "' AND `STRGTID` = '" & STRGTID & "' AND `SBRGTID` = '" & SBRGTID & "'").Rows.Count = 0 Then
            'MsgBox("ไม่ซ้ำ")

            Dim queryString As String
            queryString = "INSERT INTO prdprice "
            queryString += "(prdcode, mtrgtid, strgtid, sbrgtid, proid, f_pro, opdprc, ipdprc, stdate, endate, status) "
            queryString += "VALUES ("
            queryString += "'" & PRDCODE & "', "
            queryString += "'" & MTRGTID & "', "

            If STRGTID <> 0 Then
                queryString += "'" & STRGTID & "', "
            Else
                queryString += "null, "
            End If

            If SBRGTID <> 0 Then
                queryString += "'" & SBRGTID & "', "
            Else
                queryString += "null, "
            End If

            queryString += "null, "
            queryString += "'0', "
            queryString += "'" & OPDPRC & "', "
            queryString += "'" & IPDPRC & "', "


            'Dim STDATEMinusYear As Date = STDATE.AddYears(-543)
            'queryString += "'" & STDATEMinusYear.ToString("yyyy-MM-dd") & "', "
            'queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "

            queryString += "'" & STDATE.ToString("yyyy-MM-dd") & "', "
            queryString += "'" & ENDATE.ToString("yyyy-MM-dd") & "', "
            queryString += "'" & STATUS & "')"

            connect.ExecuteNonQuery(queryString)

            Try
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการกำหนดราคาคู่สัญญาเรียบร้อยแล้ว")
                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(ex.ToString)
                Return False
            End Try

            connect.Dispose()
        End If
    End Function
End Class
